Objectives and Contextualisation
This optional module, increases the knowledge acquired in the spatial analysis module of the same master, focusing on the exploitation of geographic databases from the SQL language, as well as in specific practical cases. In addition, it adds concepts associated with the publication of cartography on the Internet taking into account international standards for data and metadata that allow interoperability including semantic, technologic, information, etc.
At the end of the course, the student will be able to:
1. Use different cartography publication tools on the Internet.
2. Know the advantages and limitations of the use of standards in the GIS world.
3. Apply international standards to the edition and publication of data and metadata on the Internet.
4. Master queries in databases using SQL language.
5. Design appropriately information systems for the use of data in a scientific, professional or informative context.
Content
RELATIONAL DATABASES. SQL
1. Introduction to relational databases
2. Conceptual design of a relational database: entity-relationship model
2.1 Foundations of relational databases
2.2 Entities, attributes, instances
2.3 Primary keys and foreign keys
2.4 Types of relationships and classification
2.5 Three-valued logic
3. Logical design of a database
4. The sample database: IEFC_Garrotxa.mdb
4.1 The Ecological and Forest Inventory of Catalonia (IEFC de la Garrotxa)
5. Conceptual, logical and physical model of the IEFC database
6. Physical design of a database (standardization)
7. Practical example of designing a database for a Library
8. Advantages of a relational database: integrity of entities and referential integrity
9. Features of a Database Management System (SGDB)
10. Introduction to the SQL language (management of a BD)
10.1 What is SQL?
10.2 Advantages of SQL
11. Introduction to DML (Data Management Language) and DDL (Data Definition Language)
12. Recovery of data with SQL: SELECT statement
13. Simple inquiries (SELECT ... FROM)
13.1 Management practices with SQL databases (1)
14. Union consultations (UNION)
14.1 Database management practices with SQL (2)
15. Multi-table questions: compositions
15.1 Multi-table queries in SQL1
15.2 Internal compositions (INNER JOIN)
15.3 External compositions (LEFT, RIGHT and OUTER JOIN)
15.4 Self-composition
15.5 Management practices with SQL databases (3)
16. Summary queries
16.1 Column functions (GROUP BY)
16.2 Conditions in summary queries (HAVING)
16.3 Database management practices with SQL (4)
17. Subqueries
17.1 Comparison test with subquery
17.2 Proof of belonging to a subset of a subquery
17.3 Test of existence
17.4 Quantitative comparison test
17.5 Database management practices with SQL (5)
18. Nested consultations
18.1 Database management practices with SQL (6)
19. ODBC Link of a GIS layer with a SQL query (DSN file)
19.1 Creation of a DSN file for the database
19.2 Creation of a layer of points from the database
19.3 Creation of a link via ODBC of a SQL query with MiraMon's point layer
19.4 Creation of a link via ODBC from a SQL query with a layer of MiraMon polygons
20. Transactions (COMMIT ROLLBACK)
21. Update records:
21.1 Insertion of records (INSERT)
21.2 Delete and delete with subquery (DELETE)
21.3 Modification and modification with subquery (UPDATE)
21.4 Database management practices with SQL (7)
22. DDL (Data Definition Language)
22.1 Definition and creation of databases
22.2 Definition of tables and views
22.3 Definition of fields
22.4 Definition of restrictions
22.5 Definition of indexes
22.6 Changes in the structure of the database
22.7 Database management practices with SQL (8)
CASE STUDIES IN GIS IMPLEMENTATIONS
Contents based on a series of conferences by representatives of different public and/or private organizations that explain the design and use of the GIS in their work environments
STANDARDS FOR DISTRIBUTED GEOSERVICES
1. Introduction
1.1 Interoperability and IDES
1.2 Standardization organizations
1.3 UML and XML
Exercise 1: Introduction to XML and XML Schema (Enterprise Architech XMLValidator Buddy)
2. Metadata standards
2.1 Introduction
2.2 Dublin Core
2.3FGDC
2.4 ISO (19115, 19139)
2.5 IDEC profile
2.6 Profile NEM
2.7 INSPIRE profile
2.8 Metadata management applications
Exercise 2: Metadata documentation
3. Format standards
3.1 Modeling of data: UML and GML
Exercise 3: Introduction to GML, generation schemes from UML
3.2 Other format standards (SHP, MMZx, KML, GeoJSON, SWE Common, WaterML, ...)
Exercise 4: Google Earth, Google Maps and KML
4. GEOSERVICES STANDARDS
4.1 Catalog services: CSW
4.2 Display services: WMS, WMTS, OWS Context
4.3 Download service: WCS, WFS, SOS
4.4 Processing service: WPS
Exercise 5: Connection with external WMS servers.
PUBLISHING CARTOGRAPHY ON THE INTERNET
1. Introduction
1.1 Protocols
1.1.1 Layers of protocol
1.1.2 Client server architecture
1.1.3 Most commonly used protocol layers
1.2 Technological evolution of distributed GIS
1.2.1 Static maps (theory for exercise 0)
1.2.2 Static webpages (theory for exercise 1)
1.2.3 Interactive web maps (theory for exercise 2)
1.2.3.a Accelerated JavaScript and JSON
1.2.4 Geoservices distributed
1.3 Nearby technological examples
2. ISO and OGC standards
2.1 Introduction to WxS or OWS
2.2 Services for the evaluation of information
2.2.1 Review of the Web Map Service (theory for exercises 3, 4, 5)
2.2.2 Use of several WMS clients
2.3 Services in the cloud (exercise 6)
3. Practice
3.1 Introduction to IIS
3.2 Static map publication
3.3 Dynamic map publication
Competences
Design and apply a methodology, based on the knowledge acquired, for studying a particular use case.
Design and apply solutions based on GIS tools for managing and exploiting natural resources or administrative information with a spatial component.
Handle different data and metadata formats appropriately and take the importance of international standards into account when storing them and publishing them on internet.
Take a holistic approach to problems, offering innovative solutions and taking appropriate decisions based on knowledge and judgement.
Use acquired knowledge as a basis for originality in the application of ideas, often in a research context.
Use different specialised GIS and remote sensing software, and other related software.
Learning Outcomes
Apply international standards for editing and publishing data and metadata on internet.
Design and apply a methodology, based on the knowledge acquired, for studying a particular use case.
Design suitable information systems for handling data in scientific, professional or general-interest contexts.
Handle different tools for publishing cartography on internet.
Know the advantages and limitations of the use of standards in the GIS field.
Show expertise in querying databases using the SQL language.
Take a holistic approach to problems, offering innovative solutions and taking appropriate decisions based on knowledge and judgement.
Use acquired knowledge as a basis for originality in the application of ideas, often in a research context.